<?php declare(strict_types=1);

namespace App\Services\Count;


use App\Models\Bi\Bill;
use App\Models\Bi\BillLog;
use App\Models\Merchant;
use App\Models\Store;
use App\Models\StoreAllPayWayList;
use App\Models\User;
use App\Services\PrintSqlService;
use App\Util\TimeUtil;
use Illuminate\Support\Facades\DB;
use App\Models\Order;
use App\Models\RefundOrder;
use App\Services\PengYiPeng\SPILiteService;
use Illuminate\Support\Facades\Schema;

class MerchantBillCountService
{


    protected function searchTime(array $where)
    {
        if (!empty($where["curHdIndex"])) {
            $curHdIndex = $where["curHdIndex"];
            $timeUtil = new TimeUtil();
            list($start, $end, $desc) = $timeUtil->get((int)$curHdIndex);
        } else {
            $start = $where["startDate"];
            $end = $where["endDate"];
        }
        if (empty($start) || empty($end)) {
            throw new \Exception("请填写开始时间和结束时间");
        }
        return [$start, $end];
    }

    protected function setWhere($query, array $where): void
    {

        if (!empty($where["device_no"])) {
            $query->where("device_code", $where["device_no"]);
        }
        if (!empty($where["agent_id"])) {
            //查询代理商
            if (is_array($where["agent_id"])) {
                $query->whereIn("agent_id", $where["agent_id"]);
            } else {
                $query->where("agent_id", $where["agent_id"]);
            }
        }

        if (!empty($where["receive_model"])) {
            $query->where("receive_model", $where["receive_model"]);
        }


        if (!empty($where["store_pid"])) {
            $query->where("store_pid", $where["store_pid"]);
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn("store_id", $where["store_id"]);
            } else {
                $query->where("store_id", $where["store_id"]);
            }
        }

        if (!empty($where["pay_type"])) {
            $query->where("pay_type", $where["pay_type"]);
        }

        if (!empty($where["company"])) {
            $query->where("company", $where["company"]);
        }

        if (!empty($where["total_fee"])) {
            $query->where("total_fee", $where["total_fee"]);
        }

        if (!empty($where["total_amount"])) {
            $query->where("total_amount", $where["total_amount"]);
        }

        if (!empty($where["cashier_id"])) {
            if (is_array($where["cashier_id"])) {
                $query->whereIn("cashier_id", $where["cashier_id"]);
            } else {
                $query->where("cashier_id", $where["cashier_id"]);
            }
        }
    }

    protected function setGroup($query, array $group): void
    {
        $tmpGroup = [];
        foreach ($group as $column) {
            if (in_array($column, ["cashier_id", "store_id", "store_pid", "company", "pay_type", "day"])) {
                array_push($tmpGroup, $column);
            }
        }
        if (!empty($tmpGroup)) {
            $query->addSelect($tmpGroup);
            $query->groupBy($tmpGroup);
        }
    }

    //商户交易统计

    /**
     * @param array $where curHdIndex   startDate  endDate  agentName StoreName pay_type company  money cashier
     * @return void
     * @throws \Exception
     */
    public function merchantGet(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        // 商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $timeMappTables = $util->getTableBill($timeBatch);
        //查询不能超过92天
        $count = [
            "total_fee" => 0,
            "refund_fee" => 0,
            "fee_amount" => 0,
            "total_num" => 0,
            "refund_num" => 0,
            "refund_fee_nextday" => 0,
            "refund_num_next" => 0,
            "coupon_amount_yc" => 0,
            "coupon_num_yc" => 0,
            "coupon_amount_mc" => 0,
            "coupon_num_mc" => 0,
        ];
        foreach ($timeMappTables as $table => $time) {
            list($min, $max) = $util->getYearDayRange($time);

            $query = Bill::query()->from($table);
            //在这里设置查询条件
            $this->setWhere($query, $where);
            $query->whereBetween("day", [$min, $max]);
            $result = $query->select([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(refund_fee) as refund_fee"),//退款金额
//                DB::raw("sum(fee_amount) as fee_amount"),//手续费
                DB::raw("sum(if(company='cmbc' and receive_model=3,0,fee_amount)) as fee_amount"),//手续费
                DB::raw("sum(total_num) as total_num"), //交易笔数
                DB::raw("sum(refund_num) as refund_num"),// 退款笔数
                DB::raw("sum(if(refund_type=2,refund_fee,0)) as refund_fee_nextday"),//隔日退款
                DB::raw("count(if(refund_type=2,1,null)) as refund_num_next"),//当日退笔数
                DB::raw("sum(if(coupon_type=1,coupon_amount,0)) as coupon_amount_yc"),//优惠金额- 优惠类型 预充值优惠券
                DB::raw("count(if(coupon_type=1,1,null)) as coupon_num_yc"),//优惠笔数- 预充值优惠券
                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
                DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
            ])
                ->get()->toArray();
            foreach ($result as $v) {
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);
                $count["total_num"] += $v["total_num"];
                $count["refund_num"] += $v["refund_num"];
                $count["refund_fee_nextday"] = bcadd((string)$count["refund_fee_nextday"], (string)$v["refund_fee_nextday"], 2);
                $count["refund_num_next"] += $v["refund_num_next"];
                $count["coupon_amount_yc"] = bcadd((string)$count["coupon_amount_yc"], (string)$v["coupon_amount_yc"], 2);
                $count["coupon_num_yc"] += $v["coupon_num_yc"];
                $count["coupon_amount_mc"] = bcadd((string)$count["coupon_amount_mc"], (string)$v["coupon_amount_mc"], 2);
                $count["coupon_num_mc"] += $v["coupon_num_mc"];
            }
        }

        $count["merchant_actual_receipt"] = bcsub((string)$count["total_fee"], (string)$count["refund_fee"], 2);
        $count["merchant_actual_receipt"] = bcsub((string)$count["merchant_actual_receipt"], (string)$count["coupon_amount_mc"], 2);
        $count["merchant_actual_amount"] = bcsub((string)$count["merchant_actual_receipt"], (string)$count["fee_amount"], 2);
        $count["today_refund_amount"] = bcsub((string)$count["refund_fee"], (string)$count["refund_fee_nextday"], 2);
        $count["today_refund_num"] = bcsub((string)$count["refund_num"], (string)$count["refund_num_next"], 2);
        return $count;
    }


    public function merchantGetTime(array $where): array
    {
        $util = new TimeUtil();
        $start = $where["startDate"];
        $end = $where["endDate"];
        //不能跨月
        if (date("Ym", strtotime($start)) != date("Ym", strtotime($end))) {
            throw new \Exception("不能跨月查下去");
        }
        // 商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
        //按照时间批量查询
        //选择对应的表
        $tableName = $util->getBillLogTableName($start);
        //根据范围计算出所有的day
        //查询不能超过92天
        $count = [
            "total_fee" => 0,
            "refund_fee" => 0,
            "fee_amount" => 0,
            "total_num" => 0,
            "refund_num" => 0,
            "refund_fee_nextday" => 0,
            "refund_num_next" => 0,
            "coupon_amount_yc" => 0,
            "coupon_num_yc" => 0,
            "coupon_amount_mc" => 0,
            "coupon_num_mc" => 0,
        ];
        $query = BillLog::query()->from($tableName);
        //在这里设置查询条件
        $this->setWhere($query, $where);
        $query->whereIn("day", $util->getDayRange([$start, $end]));
        $query->whereBetween("pay_time", [$start, $end]);
        $result = $query->select([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
            DB::raw("sum(total_fee) as total_fee"),//交易金额
            DB::raw("sum(refund_fee) as refund_fee"),//退款金额
            DB::raw("sum(if(company='cmbc' and receive_model=3,0,fee_amount)) as fee_amount"),//手续费
            DB::raw("count(if(status=1,1,null)) as total_num"), //交易笔数
            DB::raw("count(if(status=2,1,null)) as refund_num"),// 退款笔数
            DB::raw("sum(if(refund_type=2,refund_fee,0)) as refund_fee_nextday"),//隔日退款
            DB::raw("count(if(refund_type=2,1,null)) as refund_num_next"),//当日退笔数
            DB::raw("sum(if(coupon_type=1,coupon_amount,0)) as coupon_amount_yc"),//优惠金额- 优惠类型 预充值优惠券
            DB::raw("count(if(coupon_type=1,1,null)) as coupon_num_yc"),//优惠笔数- 预充值优惠券
            DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
            DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
        ])
            ->first()->toArray();
        $v = $result;
        $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
        $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);
        $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);
        $count["total_num"] += $v["total_num"];
        $count["refund_num"] += $v["refund_num"];
        $count["refund_fee_nextday"] = bcadd((string)$count["refund_fee_nextday"], (string)$v["refund_fee_nextday"], 2);
        $count["refund_num_next"] += $v["refund_num_next"];
        $count["coupon_amount_yc"] = bcadd((string)$count["coupon_amount_yc"], (string)$v["coupon_amount_yc"], 2);
        $count["coupon_num_yc"] += $v["coupon_num_yc"];
        $count["coupon_amount_mc"] = bcadd((string)$count["coupon_amount_mc"], (string)$v["coupon_amount_mc"], 2);
        $count["coupon_num_mc"] += $v["coupon_num_mc"];
        $count["merchant_actual_receipt"] = bcsub((string)$count["total_fee"], (string)$count["refund_fee"], 2);
        $count["merchant_actual_receipt"] = bcsub((string)$count["merchant_actual_receipt"], (string)$count["coupon_amount_mc"], 2);
        $count["merchant_actual_amount"] = bcsub((string)$count["merchant_actual_receipt"], (string)$count["fee_amount"], 2);
        $count["today_refund_amount"] = bcsub((string)$count["refund_fee"], (string)$count["refund_fee_nextday"], 2);
        $count["today_refund_num"] = bcsub((string)$count["refund_num"], (string)$count["refund_num_next"], 2);
        return $count;
    }

    //在这里查看账单流水

    public function liteDeviceLog(array $pagesize, array $where): array
    {
        $page = (int)($pagesize["page"] ?? 1);
        $pagesize = (int)($pagesize["pagesize"] ?? 10);


        //确定startDate  和  endDate
        $util = new TimeUtil();
        //按照时间批量查询
        //选择对应的表
        $tableMonth = $util->getBillLogTableName($where["date"]);
        $query = BillLog::query()->from($tableMonth);
        $this->setWhere($query, $where);
        $query->where("day", date("Y-m-d", strtotime($where["date"])));
        $total = (clone $query)->count();
        $lists = (clone $query)->offset(($page - 1) * $pagesize)->limit($pagesize)->get()->toArray();
        $orders = [];
        $refunds = [];
        //整理订单和退款订单
        foreach ($lists as $v) {
            array_push($orders, $v["out_trade_no"]);
            if ($v["status"] == 2) {
                array_push($refunds, $v["out_trade_no"]);
            }
        }
        $orderTable = "orders";
        //orders_20230501
        if (Schema::hasTable($orderTable . date("Ymd", strtotime($where["date"])))) {
            $orderTable = $orderTable . date("Ymd", strtotime($where["date"]));
        }

        $orders = Order::query()->from($orderTable)
            ->where("store_id", $where["store_id"])
            ->where("device_id", $where["device_no"])
            ->whereBetween("created_at", [date("Y-m-d 00:00:00", strtotime($where["date"])), date("Y-m-d 23:59:59", strtotime($where["date"]))])
            ->whereIn("out_trade_no", $orders)
            ->get();

        $orders = array_combine(array_column($orders->toArray(), "out_trade_no"), $orders->toArray());
        //查询退款订单
        $refundOrder = RefundOrder::query()->where("store_id", $where["store_id"])
            ->whereBetween("created_at", [date("Y-m-d 00:00:00", strtotime($where["date"])), date("Y-m-d 23:59:59", strtotime($where["date"]))])
            ->whereIn("out_trade_no", $refunds)
            ->get();
        $refundOrder = array_combine(array_column($refundOrder->toArray(), "refund_no"), $refundOrder->toArray());
        $bilogs = [];
        $service = new SPILiteService();
        foreach ($lists as $v) {
            $order_type = "PAY";
            if ($v["status"] == 2) {
                $order_type = "REFUND ";
            }
            $bOrder = $orders[$v["out_trade_no"]] ?? [];
            $bRefundOrder = $refundOrder[$v["refund_no"]] ?? [];
            $order = [
                "order_type" => $order_type,
//                "merchant_order_no" => $bOrder["other_no"] ?? "",
                "isv_order_no" => $v["out_trade_no"],
//                "channel_order_no" => $bOrder["trade_no"] ?? "",
//                "merchant_refund_no" => $v["refund_no"],

                "channel_type" => $service->channel_type($bOrder),
                "total_amount" => (float)$bOrder["total_amount"] ?? 0,
                "receipt_amount" => (float)$bOrder["total_amount"] ?? 0,
                "buyer_pay_amount" => (float)$bOrder["total_amount"] ?? 0,
                "order_time" => ($v["status"] == 1) ? $v["pay_time"] : (string)$bRefundOrder["created_at"] ?? "",
                "terminal_id" => $v["device_code"],
//                "cashier_id" => "1512315441",
//                "cashier_name" => "小花",
//                "subject" => "交易概述",
//                "description" => "订单描述信息"
            ];

//            receipt_amount	Number	可选	15	实收金额，单位元，精确到小数点后2位 (PAY状态必填）	1.00
//buyer_pay_amount	Number	可选	15	买家付款金额，单位元，精确到小数点后2位（PAY状态必填）	1.00
//refund_amount	Number	可选	15	商家退款金额，单位元，精确到小数点后2位（REFUND 状态必填）


            if ($v["status"] == 2) {
                $refund = [
                    "refund_amount" => $bRefundOrder["refund_amount"] ?? 0,
                    "isv_refund_no" => $v["refund_no"],
//                "channel_refund_no" => $v["refund_no"],
//                    "pay_code" => $bOrder["auth_code"] ?? "",
                ];
                $order = array_merge($order, $refund);
            }
            $bilogs[] = $order;
        }
        return [$total, $bilogs];
    }


    public function staticGroupCountList(array $where, array $group): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $timeMappTables = $util->getTableBill($timeBatch);
        $list = [];
        $countTemplate = [
            "total_fee" => 0,
            "total_num" => 0,
            "refund_fee" => 0,
            "refund_num" => 0,
            "fee_amount" => 0
        ];
//        PrintSqlService::listen();
        foreach ($timeMappTables as $table => $times) {
            list($min, $max) = $util->getYearDayRange($times);
            $query = Bill::query()->from($table);
            $query->from(DB::raw(env("BI_DB_DATABASE") . "_" . $table . ' FORCE INDEX (`day`)'));
            $query->whereBetween("day", [$min, $max]);
            $this->setWhere($query, $where);
            $this->setGroup($query, $group);
            $query->select($group);
            $storeResults = $query->addSelect([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(total_num) as total_num"), //交易笔数
                DB::raw("sum(refund_fee) as refund_fee"),//退款金额
                DB::raw("sum(refund_num) as refund_num"),// 退款笔数
//                DB::raw("sum(fee_amount) as fee_amount"),//手续费
                DB::raw("sum(if(company='cmbc' and receive_model=3,0,fee_amount)) as fee_amount"),//手续费
                //支付宝
            ])
                ->get()->toArray();
            foreach ($storeResults as $v) {
                //在这里设置统计模板
                $key = "";
                foreach ($group as $column) {
                    $key .= $v[$column];
                }
                $count = $list[$key]["count"] ?? $countTemplate;
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["total_num"] += $v["total_num"];
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);
                $count["refund_num"] += $v["refund_num"];
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);

                unset($v["total_fee"]);
                unset($v["total_num"]);
                unset($v["refund_fee"]);
                unset($v["refund_num"]);
                unset($v["fee_amount"]);

                $list[$key] = $v;
                $list[$key]["count"] = $count;
            }
        }
        //处理分组的数据
        $storeIds = [];
        $merchantIds = [];
        $companys = [];
        if (!empty($group)) {
            foreach ($group as $column) {
                if ($column == "store_pid") {
                    $storeIds = array_merge($storeIds, array_column($list, "store_pid"));
                } else if ($column == "store_id") {
                    $storeIds = array_merge($storeIds, array_column($list, "store_id"));
                } else if ($column == "cashier_id") {
                    $merchantIds = array_merge($merchantIds, array_column($list, "cashier_id"));
                } else if ($column == "company") {
                    $companys = array_merge($companys, array_column($list, "company"));
                }
            }
        }
        $storeIds = array_unique($storeIds);
        $merchantIds = array_unique($merchantIds);
        $companys = array_unique($companys);

        if (!empty($storeIds)) {
            $stores = Store::query()->select(["id", "store_id", "pid", "store_name"])->whereIn("store_id", $storeIds)->get()->toArray();
            $stores = array_combine(array_column($stores, "store_id"), $stores);
        }

        if (!empty($merchantIds)) {
            $merchants = Merchant::query()->select(["id", "name"])->whereIn("id", $merchantIds)->get()->toArray();
            $merchants = array_combine(array_column($merchants, "id"), $merchants);
        }

        if (!empty($companys)) {
            $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
            $companys = array_combine(array_column($companys, "company"), $companys);
        }
        $result = [];
        foreach ($list as $v) {
            $v = (array)$v;
            $v["store_pid_name"] = "";
            $v["store_id_name"] = "";
            $v["cashier_name"] = "";
            $v["company_name"] = "";
            if (isset($v["store_pid"]) && isset($stores[$v["store_pid"]])) {
                $v["store_pid_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            }
            if (isset($v["store_id"]) && isset($stores[$v["store_id"]])) {
                $v["store_id_name"] = $stores[$v["store_id"]]["store_name"] ?? "";
            }
            if (isset($v["cashier_id"]) && isset($merchants[$v["cashier_id"]])) {
                $v["cashier_name"] = $merchants[$v["cashier_id"]]["name"] ?? "";
            }
            if (isset($v["company"]) && isset($companys[$v["company"]])) {
                $v["company_name"] = $companys[$v["company"]]["company_desc"] ?? "";
            }
            $result[] = $v;
        }
        return $result;
    }


    //小程序分组统计
    public function staticGroupCountListBak(array $where, string $groupType, array $group): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        $query = DB::connection("bi")->table("bills");
//        $query->when($groupType==""), function ($query) {
        $query->from(DB::raw(env("BI_DB_DATABASE") . '_bills FORCE INDEX (`day`)'));
//        });
        //在这里设置查询条件
        $query->whereBetween("day", [$start, $end]);
        $this->setWhere($query, $where);

        $this->setGroup($query, $group);

        $query->select($group);
        //嵌套查询
        $total = Bill::query()->fromSub($query, "tmp")->count();
        //设置
        $list = $query->addSelect([
//          商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
            DB::raw("sum(total_fee) as total_fee"),//交易金额
            DB::raw("sum(refund_fee) as refund_fee"),//退款金额
            DB::raw("sum(fee_amount) as fee_amount"),//手续费
            DB::raw("sum(total_num) as total_num"), //交易笔数
            DB::raw("sum(refund_num) as refund_num"),// 退款笔数
//            DB::raw("sum(fee_amount) as fee_amount"),
            DB::raw("sum(if(company='cmbc' and receive_model=3,0,fee_amount)) as fee_amount"),//手续费
//                DB::raw("sum(if(refund_type=2,refund_fee,0)) as refund_fee_nextday"),//隔日退款
//                DB::raw("count(if(refund_type=2,1,null)) as refund_num_next"),//当日退笔数
//                DB::raw("sum(if(coupon_type=1,coupon_amount,0)) as coupon_amount_yc"),//优惠金额- 优惠类型 预充值优惠券
//                DB::raw("count(if(coupon_type=1,1,null)) as coupon_num_yc"),//优惠笔数- 预充值优惠券
//                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
//                DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
            //查询分润情况
//            DB::raw("sum(" . $where["profit_column"] . ") as profit_column"),
//            DB::raw("sum(" . $where["fee_column"] . ") as fee_column")
        ])
            ->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])
            ->orderBy("total_fee", "desc")
            ->get()->toArray();
        //处理分组的数据
        $storeIds = [];
        $merchantIds = [];
        $companys = [];
        if (!empty($group)) {
            foreach ($group as $column) {
                if ($column == "store_pid") {
                    $storeIds = array_merge($storeIds, array_column($list, "store_pid"));
                } else if ($column == "store_id") {
                    $storeIds = array_merge($storeIds, array_column($list, "store_id"));
                } else if ($column == "cashier_id") {
                    $merchantIds = array_merge($merchantIds, array_column($list, "cashier_id"));
                } else if ($column == "company") {
                    $companys = array_merge($companys, array_column($list, "company"));
                }
            }
        }
        $storeIds = array_unique($storeIds);
        $merchantIds = array_unique($merchantIds);
        $companys = array_unique($companys);

        if (!empty($storeIds)) {
            $stores = Store::query()->select(["id", "store_id", "pid", "store_name"])->whereIn("store_id", $storeIds)->get()->toArray();
            $stores = array_combine(array_column($stores, "store_id"), $stores);
        }

        if (!empty($merchantIds)) {
            $merchants = Merchant::query()->select(["id", "name"])->whereIn("id", $merchantIds)->get()->toArray();
            $merchants = array_combine(array_column($merchants, "id"), $merchants);
        }

        if (!empty($companys)) {
            $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
            $companys = array_combine(array_column($companys, "company"), $companys);
        }

        foreach ($list as $k => $v) {
            $v = (array)$v;
            $v["store_pid_name"] = "";
            $v["store_id_name"] = "";
            $v["cashier_name"] = "";
            $v["company_name"] = "";
            if (isset($v["store_pid"]) && isset($stores[$v["store_pid"]])) {
                $v["store_pid_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            }
            if (isset($v["store_id"]) && isset($stores[$v["store_id"]])) {
                $v["store_id_name"] = $stores[$v["store_id"]]["store_name"] ?? "";
            }
            if (isset($v["cashier_id"]) && isset($merchants[$v["cashier_id"]])) {
                $v["cashier_name"] = $merchants[$v["cashier_id"]]["name"] ?? "";
            }
            if (isset($v["company"]) && isset($companys[$v["company"]])) {
                $v["company_name"] = $companys[$v["company"]]["company_desc"] ?? "";
            }
            $list[$k] = $v;
        }
        return [
            "total" => $total,
            "data" => $list
        ];
    }


    //小程序统计
    public function staticCount(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        // 商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $timeMappTables = $util->getTableBill($timeBatch);
        //查询不能超过92天
        $count = [
            "total_fee" => 0,
            "refund_fee" => 0,
            "fee_amount" => 0,
            "coupon_amount_mc" => 0,
            "total_num" => 0,
            "refund_num" => 0,
        ];
        foreach ($timeMappTables as $table => $time) {
            list($min, $max) = $util->getYearDayRange($time);

            $query = Bill::query()->from($table);
            //在这里设置查询条件
            $this->setWhere($query, $where);
            $query->whereBetween("day", [$min, $max]);
            $result = $query->select([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(refund_fee) as refund_fee"),//退款金额
                DB::raw("sum(total_num) as total_num"), //交易笔数
                DB::raw("sum(refund_num) as refund_num"),// 退款笔数
//                DB::raw("sum(fee_amount) as fee_amount"),//手续费
                DB::raw("sum(if(company='cmbc' and receive_model=3,0,fee_amount)) as fee_amount"),//手续费
                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 预充值优惠券
            ])
                ->get()->toArray();
            foreach ($result as $v) {
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);
                $count["coupon_amount_mc"] = bcadd((string)$count["coupon_amount_mc"], (string)$v["coupon_amount_mc"], 2);
                $count["total_num"] += $v["total_num"];
                $count["refund_num"] += $v["refund_num"];
            }
        }
        $count["receipt_amount"] = bcsub((string)$count["total_fee"], (string)$count["refund_fee"], 2);
        $count["receipt_amount"] = bcsub((string)$count["receipt_amount"], (string)$count["coupon_amount_mc"], 2);
        $count["settlement_amount"] = bcsub((string)$count["receipt_amount"], (string)$count["fee_amount"], 2);
        $count["settlement_amount"] = bcsub((string)$count["settlement_amount"], (string)$count["coupon_amount_mc"], 2);
        return $count;
    }


    //收银记录
    public function staticBillLogList(array $where): array
    {
//    PrintSqlService::listen();
        list($start, $end) = $this->searchTime($where);
//        $timeBatch = $util->rangeTimeYear($start, $end);
//        $timeMappTables = $util->getTableBill($timeBatch);
        $query = BillLog::query();
        $query->when(!empty($where["store_pid"]) && empty($where["store_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_DATABASE") . '_bill_logs FORCE INDEX (`store_pid`)'));
        });
        $query->when(!empty($where["cashier_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_DATABASE") . '_bill_logs FORCE INDEX (`merchant_id`)'));
        });
        //在这里设置查询条件
        $query->whereBetween("day", [$start, $end]);
        $this->setWhere($query, $where);
        //嵌套查询
        //设置
        $list = $query->addSelect([
            "id", "store_pid", "store_id", "cashier_id", "out_trade_no", "refund_no", "day", "pay_time", "status", "pay_type",
            "total_fee",
            DB::raw("if(company='cmbc' and receive_model=3,0,fee_amount) as fee_amount"),//手续费
            DB::raw("if(company='cmbc' and receive_model=3,0,rate) as rate"),//手续费
            "refund_fee", "receive_model", "refund_type", "company"
        ])
            ->with("refundLog")
            ->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])
            ->orderBy("id", "desc")
            ->get()->toArray();

        $total = $query->count();

        //处理分组的数据
        $storeIds = [];
        $merchantIds = [];
        $companys = [];
        $storeIds = array_merge($storeIds, array_column($list, "store_pid"));
        $storeIds = array_merge($storeIds, array_column($list, "store_id"));
        $merchantIds = array_merge($merchantIds, array_column($list, "cashier_id"));
        $companys = array_merge($merchantIds, array_column($list, "company"));

        $storeIds = array_unique($storeIds);
        $merchantIds = array_unique($merchantIds);
        $companys = array_unique($companys);

        if (!empty($storeIds)) {
            $stores = Store::query()->select(["id", "store_id", "pid", "store_name"])->whereIn("store_id", $storeIds)->get()->toArray();
            $stores = array_combine(array_column($stores, "store_id"), $stores);
        }

        if (!empty($merchantIds)) {
            $merchants = Merchant::query()->select(["id", "name"])->whereIn("id", $merchantIds)->get()->toArray();
            $merchants = array_combine(array_column($merchants, "id"), $merchants);
        }

        if (!empty($companys)) {
            $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
            $companys = array_combine(array_column($companys, "company"), $companys);
        }

        foreach ($list as $k => $v) {
            $v = (array)$v;
            $v["store_pid_name"] = "";
            $v["store_id_name"] = "";
            $v["cashier_name"] = "";
            $v["company_name"] = "";
            if (isset($v["store_pid"]) && isset($stores[$v["store_pid"]])) {
                $v["store_pid_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            }
            if (isset($v["store_id"]) && isset($stores[$v["store_id"]])) {
                $v["store_id_name"] = $stores[$v["store_id"]]["store_name"] ?? "";
            }
            if (isset($v["cashier_id"]) && isset($merchants[$v["cashier_id"]])) {
                $v["cashier_name"] = $merchants[$v["cashier_id"]]["name"] ?? "";
            }
            if (isset($v["company"]) && isset($companys[$v["company"]])) {
                $v["company_name"] = $companys[$v["company"]]["company_desc"] ?? "";
            }
            $list[$k] = $v;
        }
        return [$list, $total];
    }


    public function staticBillLogListUnionAll(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        $timeBatch = $util->rangeTimeMonth($start, $end);
        $timeMappTables = $util->getTableBillLog($timeBatch);
        //在这里判断第一个key
        $firstTableName = array_key_first($timeMappTables);
        $range = $timeMappTables[$firstTableName];
        list($min, $max) = $util->getYearDayRange($range);
        $query = DB::connection("bi")->table($firstTableName);
        $query->when(!empty($where["store_pid"]) && empty($where["store_id"]), function ($query) use ($firstTableName) {
            $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $firstTableName . ' FORCE INDEX (`store_pid`)'));
        });
        $query->when(!empty($where["cashier_id"]), function ($query) use ($firstTableName) {
            $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $firstTableName . ' FORCE INDEX (`merchant_id`)'));
        });

        //设置查询条件
        //在这里设置查询条件
//        $query->whereBetween("day", [$min, $max]);
        $query->whereBetween("day", [$min, $max]);
        $this->setWhere($query, $where);
        $select = [
            "id", "store_pid", "store_id", "cashier_id", "out_trade_no", "refund_no", "day", "pay_time", "status", "pay_type",
            "total_fee",
            DB::raw("if(company='cmbc' and receive_model=3,0,fee_amount) as fee_amount"),//手续费
            DB::raw("if(company='cmbc' and receive_model=3,0,rate) as rate"),//手续费
            "refund_fee", "receive_model", "refund_type", "company"
        ];
        $query->addSelect($select);
        //在这里使用union拼接sql
        foreach ($timeMappTables as $table => $range) {
            if ($firstTableName == $table) {
                continue;
            }
            list($min, $max) = $util->getYearDayRange($range);
            $union = DB::connection("bi")->table($table);
            $union->when(!empty($where["store_pid"]) && empty($where["store_id"]), function ($query) use ($table) {
                $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $table . ' FORCE INDEX (`store_pid`)'));
            });
            $union->when(!empty($where["cashier_id"]), function ($query) use ($table) {
                $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $table . ' FORCE INDEX (`merchant_id`)'));
            });
            //设置查询条件
            //在这里设置查询条件
            $union->whereBetween("day", [$min, $max]);
            $union->addSelect($select);
            $this->setWhere($union, $where);
            $query->unionAll($union);
        }
        $total = BillLog::query()->fromSub($query, "tmp_count")->count();
        //嵌套查询
        $list = $query
            ->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])
            ->orderBy("id", "desc")
            ->get()->toArray();
        //处理分组的数据
        $storeIds = [];
        $merchantIds = [];
        $companys = [];
        $storeIds = array_merge($storeIds, array_column($list, "store_pid"));
        $storeIds = array_merge($storeIds, array_column($list, "store_id"));
        $merchantIds = array_merge($merchantIds, array_column($list, "cashier_id"));
        $companys = array_merge($merchantIds, array_column($list, "company"));

        $storeIds = array_unique($storeIds);
        $merchantIds = array_unique($merchantIds);
        $companys = array_unique($companys);

        if (!empty($storeIds)) {
            $stores = Store::query()->select(["id", "store_id", "pid", "store_name"])->whereIn("store_id", $storeIds)->get()->toArray();
            $stores = array_combine(array_column($stores, "store_id"), $stores);
        }

        if (!empty($merchantIds)) {
            $merchants = Merchant::query()->select(["id", "name"])->whereIn("id", $merchantIds)->get()->toArray();
            $merchants = array_combine(array_column($merchants, "id"), $merchants);
        }

        if (!empty($companys)) {
            $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
            $companys = array_combine(array_column($companys, "company"), $companys);
        }

        foreach ($list as $k => $v) {
            $v = (array)$v;
            $v["store_pid_name"] = "";
            $v["store_id_name"] = "";
            $v["cashier_name"] = "";
            $v["company_name"] = "";
            if (isset($v["store_pid"]) && isset($stores[$v["store_pid"]])) {
                $v["store_pid_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            }
            if (isset($v["store_id"]) && isset($stores[$v["store_id"]])) {
                $v["store_id_name"] = $stores[$v["store_id"]]["store_name"] ?? "";
            }
            if (isset($v["cashier_id"]) && isset($merchants[$v["cashier_id"]])) {
                $v["cashier_name"] = $merchants[$v["cashier_id"]]["name"] ?? "";
            }
            if (isset($v["company"]) && isset($companys[$v["company"]])) {
                $v["company_name"] = $companys[$v["company"]]["company_desc"] ?? "";
            }
            $list[$k] = $v;
        }
        return [$list, $total];
    }
}